/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dao.exception.DAOExamenException;
import dao.interfaces.DAOExamen;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Examen;
import model.Cour;

/**
 *
 * @author Paul
 */
public class DAOExamenImpl implements DAOExamen {

    private DAOInit daoConnection;

    public DAOExamenImpl(DAOInit daoConnection) {
        this.daoConnection = daoConnection;
    }

    public ArrayList<Examen> getAllExamFromCour(int idCour) throws DAOExamenException {
        String query = "SELECT DISTINCT id_examen FROM  cour_exam_asso  where id_cour = " + idCour + "";
        PreparedStatement st = null;
        int id_exam = 0;
        try {
            Connection conn = daoConnection.getConnection();
            st = conn.prepareStatement(query);
            ResultSet res = st.executeQuery(query);
            while (res.next()) {
                id_exam = res.getInt("id_examen");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        String query2 = "SELECT e.nom, e.id_examen FROM examen e WHERE e.id_examen = " + id_exam + "";
        return findGeneral(query2);
    }

    public Examen getExam(String nomExam, int idCour) throws DAOExamenException {//récupère un exam à partir de son nom et de l'ID du cour
        String query = "SELECT e.id_examen,e.nom FROM examen e, cour c, cour_exam_asso cea where cea.id_examen=e.id_examen and cea.id_cour = c.id_cour and e.nom=" + nomExam + " and c.id_cour=" + idCour + ";";
        ArrayList<Examen> array = findGeneral(query);
        if (array.isEmpty()) {
            throw new DAOExamenException("Impossible de récupérer les examens : contacter l'administrateur");
        }
        return array.get(0);
    }

    public boolean createCourExam(int idCour, String nomExam) throws DAOExamenException {
        int idExamen = create(nomExam);
        boolean del = false;
        String query = "INSERT INTO `cour_exam_asso`(`id_cour`, `id_examen`) VALUES (?,?)";//preparation de la requete
        /**
         * initialisation des variables *
         */
        int nb = 0;
        PreparedStatement ps = null;

        /**
         * lancement de la connexion à la bd et de la requete *
         */
        try {
            Connection conn = daoConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete

            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
            ps.setInt(1, idCour);
            ps.setInt(2, idExamen);
            nb = ps.executeUpdate();//lancement de la requête
            if (nb >= 1) {
                del = true;
            }
        } catch (SQLException e) {
            throw new DAOExamenException("Examen déjà existant pour ce cours");
        }
        return del;
    }

    public Examen find(int id) throws DAOExamenException {
        String query = "SELECT * FROM `examen` WHERE `id_examen`= " + id + "";
        Examen examen = null;
        examen = findGeneral(query).get(0);
        return examen;
    }

    public Integer create(String nomExam) throws DAOExamenException {
        String query = "INSERT INTO `examen`(`nom`) VALUES (?)";
        int nb = 0;
        int autoIncKeyFromApi = -1;
        PreparedStatement ps = null;
        int idExam = 0;

        /*lancement de la connexion à la bd et de la requete */
        try {
            Connection conn = daoConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete

            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
            ps.setString(1, nomExam);
            nb = ps.executeUpdate();//lancement de la requête
            ResultSet rs = ps.getGeneratedKeys();//recupération du résultat

            if (rs.next()) {//récuperation de l'id 
                autoIncKeyFromApi = rs.getInt(1);
                System.out.println(autoIncKeyFromApi);
                idExam = autoIncKeyFromApi;
            } else {
                throw new DAOExamenException("Erreur de création d'un examen : contacter l'administrateur");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return idExam;
    }

    public boolean update(Examen obj) {
        String query = "UPDATE `examen` SET `id_examen`= ?,`nom`= ? WHERE `id_examen` = ?";
        int nb = 0;
        PreparedStatement ps = null;
        boolean del = false;
        try {
            Connection conn = daoConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete
            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
            ps.setInt(1, obj.getId());
            ps.setString(2, obj.getNomExamen());
            ps.setInt(3, obj.getId());
            System.out.println(obj.getId());
            nb = ps.executeUpdate();//lancement de la requête
            if (nb >= 1) {
                del = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return del;
    }

    public boolean delete(Examen obj) {
        boolean del = false;
        String query = "DELETE FROM `examen` WHERE `id_examen` = ?";
        Integer nb = 0;
        int autoIncKeyFromApi = -1;
        PreparedStatement ps = null;
        try {
            Connection conn = daoConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete
            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
            ps.setInt(1, obj.getId());
            System.out.println(obj.getId());
            nb = ps.executeUpdate();//lancement de la requête
            ResultSet rs = ps.getGeneratedKeys();//recupération du résultat
            if (rs.next()) {//récuperation de l'id 
                autoIncKeyFromApi = rs.getInt(1);
                System.out.println(autoIncKeyFromApi);
                System.out.println(nb);
            }
            if (nb >= 1) {
                del = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return del;

    }

    public ArrayList<Examen> getAll() throws DAOExamenException {
        String query = "SELECT e.id_examen,e.nom, c.nom FROM examen e, cour c, cour_exam_asso cea where cea.id_examen=e.id_examen and cea.id_cour = c.id_cour";
        return findGeneral(query);
    }

    public ArrayList<Examen> getExamFromCour(int id_cour) throws DAOExamenException {
        String query = "select e.id_examen,e.nom from examen e, cour_exam_asso cea where cea.id_examen=e.id_examen and cea.id_cour=" + id_cour + ";";
        return findGeneral(query);
    }

    private ArrayList<Examen> findGeneral(String requete) throws DAOExamenException {
        ArrayList<Examen> examens = new ArrayList<Examen>();
        Statement st = null;
        Examen examen = null;
        try {
            Connection conn = daoConnection.getConnection();
            st = conn.createStatement();
            ResultSet res = st.executeQuery(requete);
            while (res.next()) {
                examen = createRes(res);
                examens.add(examen);
            }
            if (examens.isEmpty()) {
                throw new DAOExamenException("Erreur de récupération des examens : contacter l'administrateur du site.");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return examens;
    }

    private Examen createRes(ResultSet res) {
        Examen examen = null;
        try {
            int idE = res.getInt("id_examen");
            String nom = res.getString("nom");
            examen = new Examen(idE, nom);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return examen;
    }
}
